﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Configuration;
using IBM.Data.DB2;

namespace DataSync.DAL
{
    /// <summary>
    /// Delegate used to provide direct access to a sqldatareader while
    /// maintaining connection management.
    /// </summary>
    /// <param name="reader">DB2 Reader</param>
    public delegate void DirectAccessDB2Delegate(DB2DataReader reader);

    public class DB2Helper : IDisposable
    {
        private string strCon;
        private DB2Connection db2Con;
        private DB2Transaction db2Trans;
        private log4net.ILog log = null;

        public DB2Helper()
        {
            this.log = log4net.LogManager.GetLogger("Debug"); //初始化
            strCon = ConfigurationManager.ConnectionStrings["db2ConnString"].ToString();
            if (string.IsNullOrEmpty(strCon))
                strCon = "Data Source=(local);Integrated Security=SSPI;Initial Catlog=ISSUE_RPT;";
            db2Con = new DB2Connection(strCon);
        }

        public DB2Helper(string ConnectionString)
        {
            strCon = ConnectionString;
            db2Con = new DB2Connection(strCon);
        }

        public void BeginTransaction()
        {
            // 确认打开连接
            Open();
            db2Trans = db2Con.BeginTransaction();
        }

        public void Commit()
        {
            try
            {
                db2Trans.Commit();
            }
            catch (Exception ex)
            {
                db2Trans.Rollback();

                //TraceHelper.WriteLine(ex);
                log.Error(ex);
                throw (ex);
            }
            finally
            {
                db2Trans.Dispose();
                db2Trans = null;
            }
        }

        public void Rollback()
        {
            try
            {
                if (db2Trans != null)
                    db2Trans.Rollback();
            }
            catch (Exception ex)
            {
                if (db2Trans != null)
                    db2Trans.Rollback();

                //TraceHelper.WriteLine(ex);
                log.Error(ex);

                throw (ex);
            }
            finally
            {
                if (db2Trans != null)
                    db2Trans.Dispose();
                db2Trans = null;
            }
        }

        private void Open()
        {
            try
            {
                if (db2Con == null)
                    db2Con = new DB2Connection(strCon);

                if (db2Con.State == ConnectionState.Closed)
                {
                    db2Con.Open();
                }
            }
            catch (Exception ex)
            {
                //TraceHelper.WriteLine(ex);
                log.Error(ex);
                throw (ex);
            }
        }

        private void Close()
        {
            if (db2Con != null)
            {
                if (db2Con.State == ConnectionState.Open)
                    db2Con.Close();
            }
        }

        /// <summary>
        /// 实现接口方法，释放资源
        /// </summary>
        public void Dispose()
        {
            try
            {
                // 确认连接是否已经关闭
                if (db2Con != null)
                {
                    if (db2Con.State == ConnectionState.Open)
                        db2Con.Close();

                    db2Con.Dispose();
                    db2Con = null;
                }
                if (db2Trans != null)
                {
                    db2Trans.Rollback();
                    db2Trans.Dispose();
                    db2Trans = null;
                }
            }
            catch (Exception ex)
            {
                log.Error(ex);
                //TraceHelper.WriteLine(ex);
            }
            finally
            {
                // 确认连接是否已经关闭
                if (db2Con != null)
                {
                    db2Con.Dispose();
                    db2Con = null;
                }
                if (db2Trans != null)
                {
                    db2Trans.Dispose();
                    db2Trans = null;
                }
            }
        }

        private DB2Command CreateCommand(string strSql, DB2Parameter[] prams)
        {
            try
            {
                // 确认打开连接
                Open();

                DB2Command cmd = new DB2Command(strSql, db2Con);
                cmd.CommandType = CommandType.Text;
                cmd.CommandTimeout = 0;

                // 依次把参数传入Sql语句
                if (prams != null)
                {
                    foreach (DB2Parameter parameter in prams)
                    {    //如果参数为null，则将其替换为System.DBNull.Value
                        if (parameter.Value == null)
                            parameter.Value = System.DBNull.Value;

                        cmd.Parameters.Add(parameter);
                    }
                }
                //事务处理
                if (db2Trans != null)
                    cmd.Transaction = db2Trans;

                return cmd;
            }

            catch (Exception ex)
            {
                //TraceHelper.WriteLine(ex, "创建Command失败");
                log.Error(ex);
                throw new ArgumentException("创建Command失败", ex);
            }
        }

        /// <summary>
        /// 执行Sql语句,结果集以DataReader的形式返回
        /// 但对DataReader的处理由handler这个委托来处理
        /// </summary>
        /// <param name="procName">Sql语句</param>
        /// <param name="handler">对DataReader进行处理的委托</param>		
        public void ExecuteSqlReader(string strSql, DirectAccessDB2Delegate handler)
        {
            ExecuteSqlReader(strSql, null, handler);
        }

        /// <summary>
        /// 执行有结果集，且有参的Sql语句，结果集以DataReader的形式返回
        /// 但对DataReader的处理由handler这个委托来处理
        /// </summary>
        /// <param name="prams">Sql语句的参数</param>
        /// <param name="strSql">Sql语句</param>
        /// <param name="handler">对DataReader进行处理的委托</param>
        public void ExecuteSqlReader(string strSql, DB2Parameter[] prams, DirectAccessDB2Delegate handler)
        {
            DB2Command cmd = null;
            try
            {
                cmd = CreateCommand(strSql, prams);
                cmd.Prepare();
                DB2DataReader reader = cmd.ExecuteReader();

                // Allow the outside world access to our reader
                if (reader != null)
                {
                    handler(reader);
                    try
                    {
                        reader.Close();
                        reader.Dispose();
                        reader = null;
                    }
                    finally
                    {
                        if (reader != null)
                        {
                            reader.Dispose();
                            reader = null;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                //TraceHelper.WriteLine(ex, string.Format("执行sql语句: {0} ，返回Reader失败", strSql));
                log.Error(ex);
                throw ex;
            }
            finally
            {
                if (cmd != null)
                {
                    cmd.Dispose();
                    cmd = null;
                }
            }
        }

        public DB2DataReader ExecuteSqlReader(string strSql)
        {
            DB2Command cmd = null;
            try
            {
                cmd = CreateCommand(strSql, null);
                cmd.Prepare();
                return cmd.ExecuteReader();

                // Allow the outside world access to our reader
                //if (reader != null)
                //{
                //    handler(reader);
                //    try
                //    {
                //        reader.Close();
                //        reader.Dispose();
                //        reader = null;
                //    }
                //    finally
                //    {
                //        if (reader != null)
                //        {
                //            reader.Dispose();
                //            reader = null;
                //        }
                //    }
                //}
            }
            catch (Exception ex)
            {
                //TraceHelper.WriteLine(ex, string.Format("执行sql语句: {0} ，返回Reader失败", strSql));
                log.Error(ex);
                throw ex;
            }
            finally
            {
                if (cmd != null)
                {
                    cmd.Dispose();
                    cmd = null;
                }
            }
        }

        public object ExecuteScalar(string strSql)
        {
            DB2Command cmd = null;
            object obj = null;

            try
            {
                cmd = CreateCommand(strSql, null);
                cmd.Prepare();

                obj = cmd.ExecuteScalar();
            }

            catch (Exception ex)
            {
                //TraceHelper.WriteLine(ex, string.Format("执行sql语句: {0} ", strSql));
                log.Error(ex);
                throw ex;
            }

            finally
            {
                if (cmd != null)
                {
                    cmd.Dispose();
                    cmd = null;
                }
            }

            return obj;
        }

        public DataSet ExecuteSqlDataSet(string strSql)
        {
            DB2Command cmd = null;
            DB2DataAdapter da = null;
            
            try
            {
                cmd = new DB2Command(strSql,db2Con);
                //cmd.Prepare();
                da = new DB2DataAdapter(cmd);
                DataSet ds = new DataSet();

                da.Fill(ds);
                
                return ds;
            }
            catch (Exception ex)
            {
                //TraceHelper.WriteLine(ex, string.Format("执行存储过程: {0} ，返回Dataset失败", strSql));
                log.Error(ex);
                throw ex;
            }
            finally
            {
                if (cmd != null)
                {
                    cmd.Dispose();
                    cmd = null;
                }
                if (da != null)
                {
                    da.Dispose();
                    da = null;
                }
                
            }
        }

    }
}
